package main;

import com.martiansoftware.jsap.JSAP;
import com.martiansoftware.jsap.JSAPException;
import com.martiansoftware.jsap.JSAPResult;
import com.martiansoftware.jsap.Switch;
import db.Database;
import db.entities.Conta;
import java.awt.Color;
import java.io.Console;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.SocketException;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.Properties;
import java.util.StringTokenizer;
import java.util.logging.ConsoleHandler;
import java.util.logging.Handler;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.mail.Folder;
import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Multipart;
import javax.mail.NoSuchProviderException;
import javax.mail.Session;
import javax.mail.Store;
import javax.mail.internet.MimeBodyPart;
import org.apache.commons.net.ftp.FTP;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPReply;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.data.category.DefaultCategoryDataset;
import org.jfree.data.time.Day;
import org.jfree.data.time.TimeSeries;
import org.jfree.data.time.TimeSeriesCollection;
import org.joda.time.DateTime;
import org.joda.time.Days;
import util.Config;
import util.CustomFormatter;
import util.GraphColors;
import util.Tools;
import util.Word;

public class controle {

    public final static Logger LOGGER = Logger.getLogger(controle.class.getName());
    private static String key = null;
    private static Connection conn = null;
    private static ArrayList<File> arquivos = new ArrayList<File>();
    private static GraphColors ds = new GraphColors();
    private static String max_s = null;
    private static int max_c = 0;

    public static void main(String[] args) throws IOException, SQLException, JSAPException {

        //createConfig();
        //System.exit(4);

        LOGGER.setLevel(Level.ALL);

        // Command line parser
        JSAP argsParser = new JSAP();

        // Only the graphs
        Switch optGraph = new Switch("graphsonly");
        optGraph.setShortFlag('g').setLongFlag("graphsonly").setHelp("Only update the html/graphs");
        argsParser.registerParameter(optGraph);

        // Regen received
        Switch optRegen = new Switch("regen");
        optRegen.setShortFlag('r').setLongFlag("regen").setHelp("Regen o received from received full");
        argsParser.registerParameter(optRegen);

        // Verbose mode
        Switch optVerbose = new Switch("verbose");
        optVerbose.setShortFlag('v').setLongFlag("verbose").setHelp("Verbose mode ON! A lot of crap on the screen.");
        argsParser.registerParameter(optVerbose);

        // Silent mode
        Switch optSilent = new Switch("silent");
        optSilent.setShortFlag('s').setLongFlag("silent").setHelp("Silent mode ON! NO OUTPUT AT ALL!");
        argsParser.registerParameter(optSilent);

        // Update word count
        Switch optWCount = new Switch("wcount");
        optWCount.setShortFlag('w').setLongFlag("wordcount").setHelp("Updates word count stats");
        argsParser.registerParameter(optWCount);

        // Upload to FTP server
        Switch optUpload = new Switch("upload");
        optUpload.setShortFlag('u').setLongFlag("upload").setHelp("Upload to FTP server");
        argsParser.registerParameter(optUpload);

        JSAPResult config = argsParser.parse(args);

        // Logger init
        Handler[] h = Logger.getLogger("").getHandlers();
        ConsoleHandler ch = (ConsoleHandler) h[0];
        ch.setFormatter(new CustomFormatter("[%t] [%L] \t %m"));
        ch.setLevel(Level.INFO);
        if (config.getBoolean("verbose")) {
            ch.setLevel(Level.ALL);
        }
        if (config.getBoolean("silent")) {
            ch.setLevel(Level.OFF);
        }

        // ------------------------------------
        // Collecting key
        try {
            System.out.println("SpamStat v.1.3");
            System.out.println("(c) copyleft... go on, do what u have to do.");
            System.out.println("");

            //Console con = System.console();
            //char[] keys = con.readPassword("Enter passkey : ");
            //key = new String(keys);
            //Arrays.fill(keys, ' ');
        } catch (Exception ex) {
            LOGGER.warning("Erro ao capturar a passkey : " + ex.getMessage());
        }

        key = "putaqueopariu";
        //System.out.println(Tools.getEncryptedByKey("", key));
        //System.exit(0);

        LOGGER.info("Logging level : " + ch.getLevel().getName());


        // -----------------------------
        // Connecting to database
        try {
            conn = Database.getInstance().getConnection();
        } catch (Exception e) {
            LOGGER.severe(e.getMessage());
            System.exit(1);
        }

        if (config.getBoolean("regen")) {
            regenReceived(conn);
        }

        if (!config.getBoolean("graphsonly")) {
            try {
                String listaConta = "SELECT * FROM `spamstat`.`accounts`";
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(listaConta);
                while (rs.next()) {
                    Conta conta = new Conta();
                    conta.setId(rs.getInt("idaccount"));
                    conta.setNome(rs.getString("nome"));
                    conta.setPorta(rs.getInt("porta"));
                    conta.setSenha(rs.getString("senha"));
                    conta.setServidor(rs.getString("servidor"));
                    conta.setUsuario(rs.getString("usuario"));
                    conta.setMethod(rs.getString("method"));
                    conta.setPasta(rs.getString("pasta"));
                    conta.setProvider(rs.getString("provider"));
                    loadEmails(conta);
                }
            } catch (NoSuchProviderException ex) {
                LOGGER.severe(ex.getMessage());
            } catch (MessagingException ex) {
                LOGGER.severe(ex.getMessage());
            }
        }



        // -------------------------------
        // Updating graphs

        // =====================================================================
        // by date
        graphByTime();

        // =====================================================================
        // by server - reply
        graphByServerReply();

        // =====================================================================
        // by sender server
        graphBySenderServer();

        // Atualiza contagem de palavras...
        if (config.getBoolean("wcount")) {
            LOGGER.info("Iniciando atualização de contagem de palavras...");
            wordCount(conn);
        }


        // =====================================================================
        // by top words
        graphByTopWords();

        // =====================================================================
        // by week day
        graphByWeekDay();

        // =====================================================================
        // max by day
        String sql_maxbyday = "select date_format(data,'%d/%m/%Y') data, max(conta) conta from (SELECT data, count(*) as conta FROM `spamstat`.`messages` group by data order by conta desc) as contador";
        Statement s1 = conn.createStatement();
        ResultSet rs1 = s1.executeQuery(sql_maxbyday);
        String max_dia = "";
        String max_dia_total = "";
        while (rs1.next()) {
            max_dia = rs1.getString("data");
            max_dia_total = rs1.getString("conta");
        }
        rs1.close();
        s1.close();
        rs1 = null;
        s1 = null;

        // =====================================================================
        // total size
        String sql_totalsize = "SELECT sum(tamanho) total FROM messages";
        s1 = conn.createStatement();
        rs1 = s1.executeQuery(sql_totalsize);
        String totalsizebyte = "";
        long tsb = 0l;
        while (rs1.next()) {
            tsb = rs1.getLong("total");
            totalsizebyte = Tools.convertPowerOfByte(tsb, "#######");
        }
        rs1.close();
        s1.close();
        rs1 = null;
        s1 = null;

        // =====================================================================
        // min by day
        String sql_minbyday = "select date_format(data,'%d/%m/%Y') data, min(conta) conta from (SELECT data, count(*) as conta FROM `spamstat`.`messages` group by data order by conta) as contador";
        s1 = conn.createStatement();
        rs1 = s1.executeQuery(sql_minbyday);
        String min_dia = "";
        String min_dia_total = "";
        while (rs1.next()) {
            min_dia = rs1.getString("data");
            min_dia_total = rs1.getString("conta");
        }
        rs1.close();
        s1.close();
        rs1 = null;
        s1 = null;


        // =====================================================================
        // Totais e desde quando...
        String sql_since = "select count(*) conta, min(data) since from messages";
        s1 = conn.createStatement();
        rs1 = s1.executeQuery(sql_since);
        String since = "";
        String contatotal = "";
        while (rs1.next()) {
            since = rs1.getString("since");
            try {
                since = Tools.DBDate2String(since);
            } catch (Exception ex) {
                LOGGER.warning(ex.getMessage());
            }
            contatotal = rs1.getString("conta");
        }
        rs1.close();
        s1.close();
        rs1 = null;
        s1 = null;

        Date past = Tools.DBDate2JavaDate(since);
        Date today = new Date(); // July 24th 
        int days = Days.daysBetween(new DateTime(past), new DateTime(today)).getDays();
        String media = String.valueOf(Integer.parseInt(contatotal) / days);
        long mediatotalsize = tsb / Long.parseLong(contatotal);
        
        HashMap<String, String> mp = new HashMap<String, String>();
        mp.put("GRAPH_BYDATE", "chart.jpg");
        mp.put("GRAPH_BYSENTSERVER", "chart2.jpg");
        mp.put("GRAPH_BYWEEKDAY", "chart3.jpg");
        mp.put("GRAPH_BYRECEIVED", "chart4.jpg");
        mp.put("GRAPH_BYWORD", "chart5.jpg");
        mp.put("MAXDAY", max_dia);
        mp.put("MAXDAYCOUNT", max_dia_total);
        mp.put("MINDAY", min_dia);
        mp.put("MINDAYCOUNT", min_dia_total);
        mp.put("MAXSENTSERVER", max_s);
        mp.put("MAXSENTSERVERCOUNT", String.valueOf(max_c));
        mp.put("DATE_SINCE", since);
        mp.put("TOTAL_SPAMS", contatotal);
        mp.put("MEDIUM_SPAMS", media);
        mp.put("TOTALSIZE", totalsizebyte);
        mp.put("MEDIUM_TOTALSIZE", Tools.convertPowerOfByte(mediatotalsize, "#######"));

        try {
            File c6 = Tools.parseTemplate("index", mp, true);
            arquivos.add(c6);
            LOGGER.info("Html gerado...");
        } catch (IOException ioe) {
            LOGGER.severe(ioe.getMessage());
        }

        if (config.getBoolean("upload")) {
            uploadProject(arquivos);
        }

        conn.close();

    }

    private static void graphByWeekDay() throws SQLException {
        String sql_byweekday = "SELECT date_format(data, '%w') sem, date_format(data,'%W') as semana, count(*) conta FROM `spamstat`.`messages` group by semana order by sem";
        Statement s1 = conn.createStatement();
        ResultSet rs1 = s1.executeQuery(sql_byweekday);
        DefaultCategoryDataset dcds = new DefaultCategoryDataset();
        while (rs1.next()) {
            dcds.setValue(rs1.getInt("conta"), "Spams", rs1.getString("semana"));
        }
        JFreeChart chart = ChartFactory.createBarChart("Spams por dia da semana", "Dia da semana", "Spams", dcds, PlotOrientation.VERTICAL, true, true, true);
        chart.getPlot().setDrawingSupplier(ds);
        File c5 = new File("chart3.jpg");
        try {
            //Paint p = new GradientPaint(0, 0, Color.WHITE,700,500, Color.GREEN);
            //chart.setBackgroundPaint(Color.CYAN);
            chart.getPlot().setBackgroundPaint(Color.WHITE);
            ChartUtilities.saveChartAsJPEG(c5, chart, 700, 500);
            arquivos.add(c5);
            LOGGER.info("Grafico gerado [week day] : chart3.jpg");
        } catch (IOException e) {
            LOGGER.severe(e.getMessage());
        }
        rs1.close();
        s1.close();
        rs1 = null;
        s1 = null;

    }

    private static void graphByTopWords() throws SQLException {
        String sql_topwords = "select * from(SELECT * FROM `spamstat`.`words` where conta > " + Config.getInstance().getString("graph.topwords.threshold") + " and LENGTH(idwords) > 3 order by conta desc) temp order by idwords";
        Statement s1 = conn.createStatement();
        ResultSet rs1 = s1.executeQuery(sql_topwords);
        DefaultCategoryDataset dcds = new DefaultCategoryDataset();
        while (rs1.next()) {
            dcds.setValue(rs1.getInt("conta"), "Total", rs1.getString("idwords"));
        }
        JFreeChart chart = ChartFactory.createBarChart("Palavras mais usadas nos títulos [threshold=" + Config.getInstance().getString("graph.topwords.threshold") + "]", "Palavra", "Total", dcds, PlotOrientation.HORIZONTAL, true, true, true);
        chart.getPlot().setDrawingSupplier(ds);
        File c4 = new File("chart5.jpg");
        try {
            ChartUtilities.saveChartAsJPEG(c4, chart, 700, 500);
            arquivos.add(c4);
            LOGGER.info("Grafico gerado [top words] : chart5.jpg");
        } catch (IOException e) {
            LOGGER.severe(e.getMessage());
        }
        rs1.close();
        s1.close();
        rs1 = null;
        s1 = null;

    }

    private static void graphBySenderServer() throws SQLException {
        String threshold = Config.getInstance().getString("graph.sentemail.threshold");
        String sql_byreceived = "SELECT received, count(*) as conta FROM `spamstat`.`messages` where received is not null group by received having conta >=" + threshold;
        Statement s1 = conn.createStatement();
        ResultSet rs1 = s1.executeQuery(sql_byreceived);
        DefaultCategoryDataset dcds = new DefaultCategoryDataset();
        while (rs1.next()) {
            dcds.setValue(rs1.getInt("conta"), "Spams", rs1.getString("received"));
        }
        JFreeChart chart = ChartFactory.createBarChart("Spams por servidor (Received From/By) [threshold=" + threshold + "]", "Servidor", "Spams", dcds, PlotOrientation.HORIZONTAL, true, true, true);
        chart.getPlot().setDrawingSupplier(ds);
        File c3 = new File("chart4.jpg");
        try {
            ChartUtilities.saveChartAsJPEG(c3, chart, 700, 500);
            arquivos.add(c3);
            LOGGER.info("Grafico gerado [sender server] : chart4.jpg");
        } catch (IOException e) {
            LOGGER.severe(e.getMessage());
        }
        rs1.close();
        s1.close();
        rs1 = null;
        s1 = null;

    }

    private static void graphByServerReply() throws SQLException {
        String threshold = Config.getInstance().getString("graph.sentemail.threshold");
        String sql_byserver = "SELECT servidor, count(*) as conta FROM `spamstat`.`messages` where servidor is not null and servidor <> '' group by servidor having conta >= " + threshold;
        Statement s1 = conn.createStatement();
        ResultSet rs1 = s1.executeQuery(sql_byserver);
        DefaultCategoryDataset dcds = new DefaultCategoryDataset();
        while (rs1.next()) {
            if (rs1.getInt("conta") > max_c) {
                max_c = rs1.getInt("conta");
                max_s = rs1.getString("servidor");
            }
            dcds.setValue(rs1.getInt("conta"), "Spams", rs1.getString("servidor"));
        }
        JFreeChart chart = ChartFactory.createBarChart("Spams por servidor (Reply-To) [threshold=" + threshold + "]", "Servidor", "Spams", dcds, PlotOrientation.HORIZONTAL, true, true, true);
        chart.getPlot().setDrawingSupplier(ds);
        File c2 = new File("chart2.jpg");
        try {
            ChartUtilities.saveChartAsJPEG(c2, chart, 700, 500);
            arquivos.add(c2);
            LOGGER.info("Grafico gerado [server - reply] : chart2.jpg");
        } catch (IOException e) {
            LOGGER.severe(e.getMessage());
        }

        rs1.close();
        s1.close();
        rs1 = null;
        s1 = null;

    }

    private static void graphByTime() throws SQLException {
        // =====================================================================
        // by date
        String sql_bydate = "SELECT date_format(data,'%Y') as ano, date_format(data,'%m') mes, date_format(data,'%d') dia, data, count(*) conta FROM `spamstat`.`messages` group by data";
        Statement s1 = conn.createStatement();
        ResultSet rs1 = s1.executeQuery(sql_bydate);
        TimeSeries ts = new TimeSeries("Spams", Day.class);
        while (rs1.next()) {
            ts.add(new Day(rs1.getInt("dia"), rs1.getInt("mes"), rs1.getInt("ano")), rs1.getInt("conta"));
        }
        rs1.close();
        s1.close();
        rs1 = null;
        s1 = null;
        TimeSeriesCollection tsc = new TimeSeriesCollection(ts);
        JFreeChart chart = ChartFactory.createTimeSeriesChart("Spams Recebidos Por Dia", "Data", "Spams", tsc, true, true, false);
        chart.getPlot().setBackgroundPaint(Color.WHITE);
        chart.getPlot().setDrawingSupplier(ds);
        File c1 = new File("chart.jpg");
        try {
            ChartUtilities.saveChartAsJPEG(c1, chart, 700, 500);
            arquivos.add(c1);
            LOGGER.info("Grafico gerado [by date] : chart.jpg");
        } catch (IOException e) {
            LOGGER.severe(e.getMessage());
        }

    }

    private static String joinReceivedFields(String[] f) {
        StringBuilder sb = new StringBuilder();
        for (String rc : f) {
            sb.append(rc);
            sb.append(System.getProperty("line.separator"));
        }
        return sb.toString();
    }

    private static String parseLastReceived(String[] header) {
        String retorno = "";
        String ignore = Config.getInstance().getString("parse.host.ignore");
        for (int i = header.length - 1; i >= 0; i--) {
            String h = clearLine(header[i]);
            LOGGER.fine("\t" + i + "\t" + h);
            if (h.startsWith("from") || h.startsWith("by")) {
                if (h.startsWith("by")) {
                    String partes[] = h.split(" ");
                    retorno = partes[1];
                } else {
                    int pos = h.indexOf("by");
                    if (pos > 0) {
                        String tmp1 = h.substring(pos);
                        String partes[] = tmp1.split(" ");
                        retorno = partes[1];
                    }
                }
                if (retorno.indexOf(ignore) > 0) {
                    String partes[] = h.split(" ");
                    retorno = partes[1];
                }
                if (retorno.indexOf(".") > 0) {
                    break;
                }
            }
        }
        retorno = smartHost(retorno);
        LOGGER.fine("Finito: " + retorno);
        return retorno;
    }

    private static String clearLine(String line) {
        String str = line.replaceAll("(\\r|\\n)", "");
        str = str.replaceAll("\\s+", " ");
        return str;
    }

    private static void regenReceived(Connection conn) throws SQLException {
        String sql = "select * from messages where received is not null";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            String received = rs.getString("received");
            String finalhost = smartHost(received);
            String sql2 = "update messages set received=? where idmessage=?";
            try {
                PreparedStatement ps = conn.prepareStatement(sql2);
                ps.setString(1, finalhost);
                ps.setString(2, rs.getString("idmessage"));
                ps.execute();
            } catch (SQLException ex) {
                LOGGER.severe("Erro ao atualizar : " + ex.getMessage());
            }
        }
        rs.close();
        stmt.close();
    }

    private static void wordCount(Connection conn) throws SQLException, IOException {
        String sql = "truncate words";
        Statement stmt = conn.createStatement();
        stmt.execute(sql);
        stmt.close();

        sql = "select assunto from messages";
        ArrayList<String> tmp = new ArrayList<String>();
        ArrayList<Word> ret = new ArrayList<Word>();
        stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            String assunto = Tools.phrasePreProcessing(rs.getString("assunto"));
            StringTokenizer st = new StringTokenizer(assunto, " ");
            while (st.hasMoreTokens()) {
                String tok = st.nextToken().trim().replaceAll("[!?:\\.,\\/'\"\\[\\]\\#\\$\\&\\(\\)\\>\\<\\+\\-\\|]", "");
                tok = tok.replaceAll("\\s+", " ");
                if (!tok.toLowerCase().startsWith("=iso") && !tok.toLowerCase().startsWith("=utf") && !tok.toLowerCase().startsWith("=windows1252")) {
                    if (tok.trim().length() > 2) {
                        tmp.add(tok.trim().toLowerCase());
                    }
                }
            }
        }
        rs.close();
        stmt.close();
        Object words[] = tmp.toArray();
        Arrays.sort(words);
        String temp = (String) words[0];
        int conta = 0;
        for (Object word : words) {
            String w = (String) word;
            if (!temp.equalsIgnoreCase(w)) {
                Word nw = new Word(temp);
                nw.setCount(conta);
                sql = "insert into words values(?,?)";
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1, temp);
                ps.setInt(2, conta);
                try {
                    ps.execute();
                    LOGGER.fine(" ++ " + nw.getWord() + " => " + conta);
                } catch (SQLException se) {
                    LOGGER.warning(" ** " + nw.getWord() + " => " + conta);
                    String sql2 = "update words set conta = conta + ? where idwords = ?";
                    PreparedStatement ps2 = conn.prepareStatement(sql2);
                    ps2.setInt(1, conta);
                    ps2.setString(2, nw.getWord());
                    try {
                        ps2.execute();
                    } catch (SQLException sqle) {
                        LOGGER.severe(" -- Fudeu... nem assim... => " + sqle.getMessage());
                    }
                }
                temp = w;
                conta = 0;
            }
            conta++;
        }
    }

    private static String smartHost(String host) {
        LOGGER.fine("===============");
        LOGGER.fine(host);
        int tam = 2;
        if (host.endsWith(".br")) {
            tam = 3;
        }
        String[] pecas = host.split("\\.");
        int control = pecas.length - 1;
        StringBuilder sb = new StringBuilder();
        while (control >= 0 && tam > 0) {
            sb.insert(0, "." + pecas[control]);
            control--;
            tam--;
        }
        String ret = sb.toString();
        if (sb.charAt(0) == '.') {
            ret = sb.substring(1);
        }
        LOGGER.info(ret);
        return ret;
    }

    public static void createConfig() {
        Console con = System.console();
        // Banner inicial!
        System.out.println("Olá, Bem-Vindo ao gerador do config.");
        System.out.println("");
        System.out.println("Agora, você deve digitar uma chave que será usada para criptografar a sua senha de email.");
        System.out.println("Ela será requisitada sempre que você executar o sistema.");

        // Chave usada para logar no sistema e encriptar as senhas
        String chave = null;
        String p1 = null;
        while (true) {
            try {
                System.out.println("");
                System.out.print("Digite a chave : ");
                char[] pass1 = con.readPassword();
                p1 = new String(pass1);
                Arrays.fill(pass1, ' ');
                System.out.print("Digite novamente : ");
                char[] pass2 = con.readPassword();
                String p2 = new String(pass2);
                Arrays.fill(pass2, ' ');

                if (p1.equals(p2)) {
                    chave = Tools.getMD5Hash(p1);
                    System.out.println("Chave setada corretamente.");
                    break;
                }
                System.out.println("As chaves não são iguas! Digite-as novamente.");
            } catch (NoSuchAlgorithmException ex) {
                LOGGER.severe(ex.getMessage());
            }
        }

        //String encrypted = Tools.getEncryptedByKey(p1, chave);

        System.out.println("Feito.");
        System.out.println("");
        System.out.println("Agora vamos as configurações do banco de dados...");

        boolean passou = false;
        while (!passou) {
            System.out.println("Apenas aperte [ENTER] para aceitar os defaults (banco mysql local sem senha de root)");
            System.out.println("");
            System.out.print("Driver JDBC [com.mysql.jdbc.Driver]: ");
            String dbdriver = con.readLine();
            if (dbdriver.trim().isEmpty()) {
                dbdriver = "com.mysql.jdbc.Driver";
            }

            System.out.print("JDBC Url [jdbc:mysql://localhost/spamstat]: ");
            String dburl = con.readLine();
            if (dburl.trim().isEmpty()) {
                dburl = "jdbc:mysql://localhost/spamstat";
            }

            System.out.print("DB User [root] : ");
            String dbuser = con.readLine();
            if (dbuser.trim().isEmpty()) {
                dbuser = "root";
            }

            System.out.print("DB Password [] : ");
            char[] dbpass1 = con.readPassword();
            String dbpass = new String(dbpass1);
            if (dbpass.trim().isEmpty()) {
                dbpass = "";
            }

            System.out.println("");
            System.out.println("Vamos tentar conectar ao banco agora...");

            try {
                Class.forName(dbdriver);
                conn = DriverManager.getConnection(dburl, dbuser, dbpass);
                passou = true;
            } catch (ClassNotFoundException e) {  //Driver não encontrado
                LOGGER.severe(e.getMessage());
                System.out.println("");
                System.out.println("Existe um erro na configuração do banco... Digite novamente!");
            } catch (SQLException e) {
                LOGGER.severe(e.getMessage());
                System.out.println("");
                System.out.println("Existe um erro na configuração do banco... Digite novamente!");
            }
        }

        // Configurações das contas de email
        System.out.println("");
        System.out.println("Agora vamos configurar as contas de email.");
        System.out.println("");
        System.out.print("Digite um nome para a conta : ");


    }

    private static void uploadProject(ArrayList<File> arquivos) {
        FTPClient ftp = new FTPClient();
        String host = Config.getInstance().getString("ftp.host");
        try {
            ftp.connect(host);
            LOGGER.info("Conectado em " + host);
            int reply = ftp.getReplyCode();
            if (!FTPReply.isPositiveCompletion(reply)) {
                ftp.disconnect();
                LOGGER.severe("FTP server refused connection.");
                throw new Exception("FTP server refused connection");
            }
            String pass = Tools.getPassByKey(Config.getInstance().getString("ftp.pass"), key);
            if (!ftp.login(Config.getInstance().getString("ftp.user"), pass)) {
                ftp.logout();
                throw new Exception("Erro no login, usuário ou senha inválidos!");
            }

            LOGGER.info("Remote system is " + ftp.getSystemType());
            ftp.setFileType(FTP.BINARY_FILE_TYPE);
            ftp.enterLocalPassiveMode();

            // start uploading...
            for (File file : arquivos) {
                InputStream input;
                LOGGER.info("Uploading... " + file.getName());
                input = new FileInputStream(file);
                if (ftp.storeFile(file.getName(), input)) {
                    LOGGER.info("Done...");
                } else {
                    LOGGER.severe("Error uploading file...");
                }
                input.close();
            }
            ftp.logout();

        } catch (SocketException ex) {
            LOGGER.severe(ex.getMessage());
        } catch (IOException ex) {
            LOGGER.severe(ex.getMessage());
        } catch (Exception ex) {
            LOGGER.severe(ex.getMessage());
        }

    }

    private static int countParts(Object content) {
        int conta = 0;
        try {
            if (content instanceof Multipart) {
                Multipart multi = ((Multipart) content);
                int parts = multi.getCount();
                for (int j = 0; j < parts; ++j) {
                    MimeBodyPart part = (MimeBodyPart) multi.getBodyPart(j);
                    if (part.getContent() instanceof Multipart) {
                        conta = conta + countParts(part.getContent());
                    } else {
                        if (!part.isMimeType("text/html") && !part.isMimeType("text/plain")) {
                            conta++;
                        }
                    }
                }
            }
        } catch(IOException e) {
            LOGGER.severe(e.getMessage());
        } catch(MessagingException e) {
            LOGGER.severe(e.getMessage());
        }
        return conta;
    }

    private static void loadEmails(Conta conta) throws NoSuchProviderException, MessagingException, SQLException {
        // ------------------------------
        // Connecting to mail server
        LOGGER.info("Checking " + conta.getNome());
        Properties props = new Properties();

        String host = conta.getServidor();
        String username = conta.getUsuario();
        String provider = conta.getProvider();

        Session session = Session.getDefaultInstance(props, null);
        Store store = session.getStore(provider);
        store.connect(host, username, conta.getPlainSenha(key));

        Folder inbox = store.getFolder(conta.getPasta());
        if (inbox == null) {
            LOGGER.severe("## Folder not found: " + conta.getPasta());
            return;
        }
        inbox.open(Folder.READ_ONLY);

        Message[] messages = inbox.getMessages();
        for (int i = 0; i < messages.length; i++) {
            Message m = messages[i];
            String add = m.getFrom()[0].toString();
            String reply2 = "";
            try {
                reply2 = add.substring(add.lastIndexOf("<") + 1, add.lastIndexOf(">"));
            } catch(StringIndexOutOfBoundsException ex) {
                LOGGER.warning("Erro ao pegar email from : " + ex.getMessage());
            }
            Calendar c = new GregorianCalendar();
            c.setTime(m.getReceivedDate());
            String dataehora = c.get(Calendar.YEAR) + "/"
                    + (c.get(Calendar.MONTH) + 1) + "/" + c.get(Calendar.DATE) + " "
                    + c.get(Calendar.HOUR_OF_DAY) + ":" + c.get(Calendar.MINUTE) + ":" + c.get(Calendar.SECOND);
            String data = c.get(Calendar.YEAR) + "/" + (c.get(Calendar.MONTH) + 1) + "/" + c.get(Calendar.DATE);
            String hora = c.get(Calendar.HOUR_OF_DAY) + ":" + c.get(Calendar.MINUTE) + ":" + c.get(Calendar.SECOND);
            LOGGER.info("=====================================================");
            LOGGER.info(messages[i].getMessageNumber() + "\t" + messages[i].getSubject());
            String sql = "insert into messages values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, m.getHeader("Message-Id")[0]);
            ps.setString(2, m.getSubject());
            ps.setString(3, reply2);
            ps.setString(4, dataehora);
            ps.setString(5, data);
            ps.setString(6, hora);
            ps.setString(7, m.getReplyTo()[0].toString());
            String lista[] = m.getHeader("List-Unsubscribe");
            String lista2 = "";
            if (lista != null) {
                lista2 = lista[0];
            }
            ps.setString(8, lista2);
            String email[] = reply2.split("@");
            String dominio = "";
            if (email.length == 2) {
                dominio = email[1];
            }
            ps.setString(9, dominio);
            ps.setInt(10, conta.getId());
            String receivedfull = joinReceivedFields(m.getHeader("Received"));
            ps.setString(11, parseLastReceived(m.getHeader("Received")));
            ps.setString(12, receivedfull);
            ps.setInt(13, m.getSize());
            int multi = 0;
            try {
                multi = countParts(m.getContent());
            } catch (IOException ex) {
                LOGGER.severe("Error processing multpart");
            }
            ps.setInt(14, multi);
            try {
                ps.execute();
            } catch (SQLException ex) {
                //if(ex instanceof java.sql.SQLIntegrityConstraintViolationException)
                //    processSizeMult(m);
                LOGGER.severe(ex.getMessage());
            }
        }
        store.close();
    }

    private static void processSizeMult(Message m) {
        String sql = "update messages set tamanho = ?, multpart = ? where idmessage = ?";
        LOGGER.info(" -- Registro já existente, processando size e multipart...");
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, m.getSize());
            LOGGER.fine(" ---- Size : " + m.getSize());
            int multi = countParts(m.getContent());
            LOGGER.fine(" ---- Multipart : " + multi);
            ps.setInt(2, multi);
            ps.setString(3, m.getHeader("Message-Id")[0]);
            ps.execute();
            ps.close();
            LOGGER.fine(" -- Done processing...");
        } catch (SQLException ex) {
            LOGGER.warning("Cant update size/multipart : " + ex.getMessage());
        } catch (MessagingException ex) {
            LOGGER.warning("Cant update size/multipart : " + ex.getMessage());
        } catch (IOException ex) {
            LOGGER.warning("Cant update size/multipart : " + ex.getMessage());
        }

    }
}
